using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Microsoft.ApplicationBlocks.Data;
using Customs.Components.KDT.ThanhKhoan.Info;

namespace Customs.Components.KDT.ThanhKhoan
{
    class HoSoThanhLy : HoSoThanhLyInfo
    {       
        public void Load()
        {
            SqlDataReader reader = Select();
            if (reader.Read())
            {
                if (!reader.IsDBNull(reader.GetOrdinal("So_TNDKDT"))) this.SoTiepNhan = reader.GetInt64(reader.GetOrdinal("So_TNDKDT"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ma_HQTN"))) this.MaHaiQuan = reader.GetString(reader.GetOrdinal("Ma_HQTN"));
                if (!reader.IsDBNull(reader.GetOrdinal("Nam_TN"))) this.NamTiepNhan = reader.GetInt16(reader.GetOrdinal("Nam_TN"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ma_DV"))) this.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("Ma_DV"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ma_BK"))) this.MaBangKe = reader.GetString(reader.GetOrdinal("Ma_BK"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ten_BK"))) this.TenBangKe = reader.GetString(reader.GetOrdinal("Ten_BK"));
                if (!reader.IsDBNull(reader.GetOrdinal("So_TNDKDT_BK"))) this.SoTiepNhanBangKe = reader.GetInt64(reader.GetOrdinal("So_TNDKDT_BK"));
                if (!reader.IsDBNull(reader.GetOrdinal("MA_HQTN_BK"))) this.MaHaiQuanTiepNhanBangKe = reader.GetString(reader.GetOrdinal("MA_HQTN_BK"));
                if (!reader.IsDBNull(reader.GetOrdinal("Nam_TN_BK"))) this.NamTiepNhanBangKe = reader.GetInt16(reader.GetOrdinal("Nam_TN_BK"));
                if (!reader.IsDBNull(reader.GetOrdinal("STTHang"))) this.STTBangKe = reader.GetInt32(reader.GetOrdinal("STTHang"));
                reader.Close();
            }
        }

        public void Load(HoSoThanhLyInfo hstl)
        {
            this._Ma_BK = hstl.MaBangKe;
            this._Ma_DV = hstl.MaDoanhNghiep;
            this._MA_HQTN = hstl.MaHaiQuan;
            this._MA_HQTN_BK = hstl.MaHaiQuanTiepNhanBangKe;
            this._Nam_TN = hstl.NamTiepNhan;
            this._Nam_TN_BK = hstl.NamTiepNhanBangKe;
            this._So_TNDKDT = hstl.SoTiepNhan;
            this._So_TNDKDT_BK = hstl.SoTiepNhanBangKe;
            this._STTHang = hstl.STTBangKe;
            this._Ten_BK = hstl.TenBangKe;
        }

        public SqlDataReader Select()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            string sql = "select * from DHOSOTL_D where So_TNDKDT=@So_TNDKDT and Ma_HQTN=@Ma_HQTN and Nam_TN=@Nam_TN and Ma_BK=@Ma_BK";
          
            SqlParameter[] parameters = new SqlParameter[4];
            parameters[0] = new SqlParameter("@So_TNDKDT", SqlDbType.BigInt);
            parameters[0].Value = this.SoTiepNhan;

            parameters[1] = new SqlParameter("@Ma_HQTN", SqlDbType.Char, 6);
            parameters[1].Value = this.MaHaiQuan;

            parameters[2] = new SqlParameter("@Nam_TN", SqlDbType.SmallInt);
            parameters[2].Value = this.NamTiepNhan;

            parameters[3] = new SqlParameter("@Ma_BK", SqlDbType.VarChar,20);
            parameters[3].Value = this.MaBangKe;

            return SqlHelper.ExecuteReader(connectionString, CommandType.Text, sql, parameters);
        }

        public static int DeleteByForeignKey(SqlTransaction transaction,string mahq,long sotiepnhan,short namtiepnhan)
        {
            string sql = "delete from DHOSOTL_D where So_TNDKDT=@So_TNDKDT and Ma_HQTN=@Ma_HQTN and Nam_TN=@Nam_TN";
            SqlParameter[] parameters = new SqlParameter[3];

            parameters[0] = new SqlParameter("@So_TNDKDT", SqlDbType.BigInt);
            parameters[0].Value = sotiepnhan;

            parameters[1] = new SqlParameter("@Ma_HQTN", SqlDbType.Char, 6);
            parameters[1].Value = mahq;

            parameters[2] = new SqlParameter("@Nam_TN", SqlDbType.SmallInt);
            parameters[2].Value = namtiepnhan;
            return SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sql, parameters);

        }

        public  int Delete(SqlTransaction transaction)
        {
            string sql = "delete from DHOSOTL_D where So_TNDKDT=@So_TNDKDT and Ma_HQTN=@Ma_HQTN and Nam_TN=@Nam_TN and Ma_BK=@Ma_BK";
            SqlParameter[] parameters = new SqlParameter[3];

            parameters[0] = new SqlParameter("@So_TNDKDT", SqlDbType.BigInt);
            parameters[0].Value = this.SoTiepNhan;

            parameters[1] = new SqlParameter("@Ma_HQTN", SqlDbType.Char, 6);
            parameters[1].Value = this.MaHaiQuan;

            parameters[2] = new SqlParameter("@Nam_TN", SqlDbType.SmallInt);
            parameters[2].Value = this.NamTiepNhan;

            parameters[3] = new SqlParameter("@Ma_BK", SqlDbType.VarChar,20);
            parameters[3].Value = this.MaBangKe;

            return SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sql, parameters);

        }
        public int Insert(SqlTransaction transaction)
        {
            StringBuilder query = new StringBuilder();
            query.Append("INSERT INTO DHOSOTL_D (");
            query.Append("So_TNDKDT, ");
            query.Append("Ma_HQTN, ");
            query.Append("Nam_TN, ");
            query.Append("MA_DV, ");
            query.Append("Ma_BK, ");
            query.Append("Ten_BK, ");
            query.Append("So_TNDKDT_BK, ");
            query.Append("MA_HQTN_BK, ");
            query.Append("Nam_TN_BK, ");
            query.Append("STTHang ) ");

            query.Append("VALUES ( ");

            query.Append("@So_TNDKDT, ");
            query.Append("@Ma_HQTN, ");
            query.Append("@Nam_TN, ");
            query.Append("@MA_DV, ");
            query.Append("@Ma_BK, ");
            query.Append("@Ten_BK, ");
            query.Append("@So_TNDKDT_BK, ");
            query.Append("@MA_HQTN_BK, ");
            query.Append("@Nam_TN_BK, ");
            query.Append("@STTHang )");

            SqlParameter[] parameters = new SqlParameter[10];

            parameters[0] = new SqlParameter("@So_TNDKDT", SqlDbType.BigInt);
            parameters[0].Value = this.SoTiepNhan;

            parameters[1] = new SqlParameter("@Ma_HQTN", SqlDbType.Char, 6);
            parameters[1].Value = this.MaHaiQuan;

            parameters[2] = new SqlParameter("@Nam_TN", SqlDbType.SmallInt);
            parameters[2].Value = this.NamTiepNhan;

            parameters[3] = new SqlParameter("@Ma_BK", SqlDbType.VarChar,20);
            parameters[3].Value = this.MaBangKe;

            parameters[4] = new SqlParameter("@Ten_BK", SqlDbType.NVarChar,255);
            parameters[4].Value = this.TenBangKe;

            parameters[5] = new SqlParameter("@So_TNDKDT_BK", SqlDbType.BigInt);
            parameters[5].Value = this.SoTiepNhan;

            parameters[6] = new SqlParameter("@MA_DV", SqlDbType.VarChar, 14);
            parameters[6].Value = this.MaDoanhNghiep;

            parameters[7] = new SqlParameter("@MA_HQTN_BK", SqlDbType.VarChar, 6);
            parameters[7].Value = this.MaHaiQuan;

            parameters[8] = new SqlParameter("@Nam_TN_BK", SqlDbType.SmallInt);
            parameters[8].Value = this.NamTiepNhan;

            parameters[9] = new SqlParameter("@STTHang", SqlDbType.Int);
            parameters[9].Value = this.STTBangKe;

            return SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, query.ToString(), parameters);

        }
        public static HoSoThanhLyInfoCollection LoadCollection(string mahq, long sotiepnhan, short namtiepnhan)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            string sql = "select * from DHOSOTL_D where So_TNDKDT=@So_TNDKDT and Ma_HQTN=@Ma_HQTN and Nam_TN=@Nam_TN";

            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@So_TNDKDT", SqlDbType.BigInt);
            parameters[0].Value = sotiepnhan;

            parameters[1] = new SqlParameter("@Ma_HQTN", SqlDbType.Char, 6);
            parameters[1].Value = mahq;

            parameters[2] = new SqlParameter("@Nam_TN", SqlDbType.SmallInt);
            parameters[2].Value = namtiepnhan;
        

            SqlDataReader reader= SqlHelper.ExecuteReader(connectionString, CommandType.Text, sql, parameters);
            HoSoThanhLyInfoCollection collection = new HoSoThanhLyInfoCollection();
            while (reader.Read())
            {
                HoSoThanhLyInfo info = new HoSoThanhLyInfo();
                if (!reader.IsDBNull(reader.GetOrdinal("So_TNDKDT"))) info.SoTiepNhan = reader.GetInt64(reader.GetOrdinal("So_TNDKDT"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ma_HQTN"))) info.MaHaiQuan = reader.GetString(reader.GetOrdinal("Ma_HQTN"));
                if (!reader.IsDBNull(reader.GetOrdinal("Nam_TN"))) info.NamTiepNhan = reader.GetInt16(reader.GetOrdinal("Nam_TN"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ma_DV"))) info.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("Ma_DV"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ma_BK"))) info.MaBangKe = reader.GetString(reader.GetOrdinal("Ma_BK"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ten_BK"))) info.TenBangKe = reader.GetString(reader.GetOrdinal("Ten_BK"));
                if (!reader.IsDBNull(reader.GetOrdinal("So_TNDKDT_BK"))) info.SoTiepNhanBangKe = reader.GetInt64(reader.GetOrdinal("So_TNDKDT_BK"));
                if (!reader.IsDBNull(reader.GetOrdinal("MA_HQTN_BK"))) info.MaHaiQuanTiepNhanBangKe = reader.GetString(reader.GetOrdinal("MA_HQTN_BK"));
                if (!reader.IsDBNull(reader.GetOrdinal("Nam_TN_BK"))) info.NamTiepNhanBangKe = reader.GetInt16(reader.GetOrdinal("Nam_TN_BK"));
                if (!reader.IsDBNull(reader.GetOrdinal("STTHang"))) info.STTBangKe = reader.GetInt32(reader.GetOrdinal("STTHang"));
                collection.Add(info);
            }
            reader.Close();
            return collection;
   
        }

    }
}
